package com.hgk.common.common.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.text.TextUtils;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;

/**
 * 数据库表方法操作类
 */
public class BaseDao {
	private Context context;

	public BaseDao(Context context) {
		this.context = context;
	}
	
	/**
	 * 获得db对象
	 */
	public SQLiteDatabase getDatabase(){
		DataBaseHelper helper = DataBaseHelper.getInstance(context);
		if(helper!=null){
			SQLiteDatabase db = helper.getReadableDatabase();
			return db;
		}else{
			return null;
		}		
	}

	/**
	 * 插入一个bean对象，此方进行数据插入时，要求数据库字段名称与bean属性名称一样
	 * 
	 * @param tableName 表名
	 * @param bean
	 * @return long 新插入数据的主键
	 */
	public synchronized long insertBean(String tableName, Object bean) {
		DataBaseHelper helper = DataBaseHelper.getInstance(context);
		if(helper==null)
			return 0;
		SQLiteDatabase db = helper.getReadableDatabase();
		ContentValues cv = BeanToCVUtil.convertBean2CV(bean);
		long key = -1;
		if (cv.size() > 0) {
			key = db.insert(tableName, null, cv);
		}
		helper.close();
		return key;
	}
	/**
	 * 插入
	 */
	public synchronized long insertContentValues(String tableName, ContentValues cv) {
		DataBaseHelper helper = DataBaseHelper.getInstance(context);
		if(helper==null)
			return 0;
		SQLiteDatabase db = helper.getReadableDatabase();
		long key = -1;
		if (cv.size() > 0) {
			key = db.insert(tableName, null, cv);
		}
		helper.close();
		return key;
	}

	/**
	 * 一次向数据库中插入一个多个bean.
	 * 
	 * @param tableName 表名称
	 * @param beanList 保存着bean的list集合
	 * @return ArrayList<Long> 每条记录存储后生成的主键
	 */
	public synchronized ArrayList<Long> insertBeanList(String tableName, List beanList) {
		DataBaseHelper helper = DataBaseHelper.getInstance(context);
		if(helper==null)
			return null;
		SQLiteDatabase db = helper.getReadableDatabase();
		ArrayList<Long> keyList = new ArrayList<Long>();
		for (Object bean : beanList) {
			ContentValues cv = BeanToCVUtil.convertBean2CV(bean);
			long key = db.insert(tableName, null, cv);
			keyList.add(key);
		}
		helper.close();
		return keyList;
	}


	/**
	 * 根据某个字段值相应的记录
	 * 
	 * @param tableName  数据库表名
	 * @param cv  要修改的属性的新的值
	 * @param attrName  字段属性名称
	 * @param attrValue 字段属性值
	 * @return 影响的行数
	 */
	public synchronized int updateByAttr(String tableName, ContentValues cv,
			String attrName, String attrValue) {
		DataBaseHelper helper = DataBaseHelper.getInstance(context);
		if(helper==null)
			return 0;
		SQLiteDatabase db = helper.getReadableDatabase();
		int number = db.update(tableName, cv, attrName + "=?",
				new String[] { attrValue });
		helper.close();
		return number;
	}
	
	/**
	 * 根据多个字段进行更新
	 * @param tableName
	 * @param cv
	 * @param attrName
	 * @param attrValue
	 * @return
	 */
	public synchronized int updateByAttrs(String tableName, ContentValues cv,
			String[] attrName, String[] attrValue) {
		DataBaseHelper helper = DataBaseHelper.getInstance(context);
		if(helper==null)
			return 0;
		SQLiteDatabase db = helper.getReadableDatabase();
		int number=0;
		String selection = null;
		if(attrName!=null && attrName.length>0){
			StringBuffer sb = new StringBuffer();
			for (int i = 0; i < attrName.length; i++) {
				if (i != (attrName.length - 1)) {
					sb.append(attrName[i] + "=" + " ? and ");
				} else {
					sb.append(attrName[i] + "=" + " ?");
				}
			}
			selection = sb.toString();
			number = db.update(tableName, cv, selection,
					attrValue);
		}
		helper.close();
		return number;
	}

	/**
	 * 根据某个字段值相应的记录
	 * 
	 * @param tableName 要操作的表
	 * @param bean 为更新表中的字段数据而提供的对象，里面存放着需要更新的数据
	 * @param columnName 要更新的字段
	 * @param columnvalue 字段的值
	 * 
	 */
	public synchronized void updateByBean(String tableName, Object bean, String columnName,
			String columnvalue) {
		DataBaseHelper helper = DataBaseHelper.getInstance(context);
		if(helper==null)
			return;
		SQLiteDatabase db = helper.getReadableDatabase();
		ContentValues values = new ContentValues();
		Field[] fields = bean.getClass().getDeclaredFields();
		for (Field field : fields) {
			field.setAccessible(true);
			try {
				if (field.getName().equals("serialVersionUID")
						|| field.getName().equals("_id")) {
					continue;
				}
				if("skillid".equals(field.getName())||"CREATOR".equals(field.getName())){//过滤技能ID字段
					continue;
				}
				
				String key = field.getName();
				String value = String.valueOf(field.get(bean));
				if (value != null && !value.equals("")) {
					values.put(key, value);
				}
			} catch (IllegalAccessException e) {
				e.printStackTrace();
			}
		}
		db.update(tableName, values, columnName + "= ?",
				new String[] { columnvalue });
		helper.close();
	}
	
	/**
	 * 根据条件查询符合条件的记录总条数
	 * 
	 * @param tableName 数所库表名
	 * @param attrNames 字段属性名称
	 * @param attrValues 字段属性值
	 * @param isLike 是否为模糊匹配
	 * @return int 总条数
	 */
	public synchronized int queryCount(String tableName, String[] attrNames, String[] attrValues,
			boolean isLike) {
		DataBaseHelper helper = DataBaseHelper.getInstance(context);
		if(helper==null)
			return 0;
		SQLiteDatabase db = helper.getReadableDatabase();
		int count = 0;
		Cursor cursor = null;
		String sql = "select count(*) from " + tableName + " ";
		// 当具有查询条件时，向sql语句后面添加查询条件
		if (attrNames!=null && attrValues!=null) {
			// 判断是否进行模糊匹配
			int nameSize = attrNames.length;
			int valueSize = attrValues.length;
			sql += "where ";
			
			for(int i=0;i<nameSize&&i<valueSize;i++){
				String name = attrNames[i];
				String value = attrValues[i];
				if (isLike) {
					attrValues[i] = "%"+attrValues[i]+"%";
					sql += name + " like ? ";
				} else {
					if("null".equals(value.trim())){
						sql += name + " is ? ";
					}else
					if("not null".equals(value)){
						sql += name + " is ? ";
					}else{
						sql += name + " = ? ";
					}
				}
				if(i<nameSize-1&&i<valueSize-1){
					sql += " and ";
				}
			}
			cursor = db.rawQuery(sql, attrValues);

		} else {
			cursor = db.rawQuery(sql, null);
		}

		// 若cursor不为空且存在数据，且取出数据
		if (null != cursor) {
			if (cursor.moveToFirst()) {
				count = cursor.getInt(0);
			}
			cursor.close();
		}
		helper.close();
		return count;
	}

	/**
	 * 对单表的查询，基本情况可满足
	 * @param tableName 表名称
	 * @param columns 查询结果返回的列 null：返回所有列
	 * @param attrNames 条件查询时所涉及到的字段名称
	 * @param selectionArgs 条件查询时对应字段的值(多条件时默认为"与"操作)
	 * @param isLike 是否是模糊匹配
	 * @param orderByAttrName 进行排序的名称(目前只支持对一个字段进行排序)
	 * @param isDesc 排序是否为降序排列(默认为升序排列)
	 * @param beginIndex  进行分页时开始的索引
	 * @param number 每页显示的数量
	 * @return cursor 查询结果游标
	 */
	private Cursor commonQuery(String tableName, String[] columns,
			String[] attrNames, String[] selectionArgs, boolean isLike,
			String orderByAttrName, boolean isDesc, String beginIndex,
			String number) {
		DataBaseHelper helper = DataBaseHelper.getInstance(context);
		if(helper==null)
			return null;
		SQLiteDatabase db = helper.getReadableDatabase();
		String selection = null;
		String orderBy = null;// 排序条件
		String limit = null;// 分页条件
		Cursor cursor = null;
		// 1.当具有查询条件时，拼装查询条件
		if (attrNames != null && attrNames.length > 0 && selectionArgs != null
				&& selectionArgs.length > 0
				&& attrNames.length == selectionArgs.length) {
			// 根据是否进行模糊匹配来判断条件语句的链接符号,默认为模糊匹配
			String joinSign = " = ";
			if (isLike) {
				joinSign = " like ";
				// 模糊匹配时将匹配值进行改造
				for (int i = 0; i < selectionArgs.length; i++) {
					selectionArgs[i] = "%" + selectionArgs[i] + "%";
				}
			}
			// 对where语句进行拼装
			StringBuffer sb = new StringBuffer();
			for (int i = 0; i < attrNames.length; i++) {
				if (i != (attrNames.length - 1)) {
					sb.append(attrNames[i] + joinSign + " ? and ");
				} else {
					sb.append(attrNames[i] + joinSign + " ?");
				}
			}
			selection = sb.toString();
		}
		// 2.组合排序条件
		if (!TextUtils.isEmpty(orderByAttrName)) {// 存在排序
			if (isDesc) {
				orderByAttrName += " desc";
			}
			orderBy = orderByAttrName;
		}
		// 3.组合结果分页条件
		if (!TextUtils.isEmpty(beginIndex) && !TextUtils.isEmpty(number)) {
			limit = beginIndex + "," + number;
		}

		// 4.执行数据库查询
		cursor = db.query(tableName, columns, selection, selectionArgs, null,
				null, orderBy, limit);
		
		helper.close();
		return cursor;
	}

	/**
	 * 根据属性值来查找相应记录
	 * 
	 * @param clazz bean对象的class
	 * @param tableName 数据库表名
	 * @param columns 查询结果中返回的字段名称，为null时，返回全部字段信息
	 * @param attrNames 字段名称,为null时，表明不带查询条件
	 * @param selectionArgs 字段值
	 * @param isLike 是否进行模糊匹配 ture:是 false:不是
	 * @param orderByAttrName 按照哪一列进行排序的字段名称
	 * @param isDesc 是否进行降序排列，默认排序为升序
	 * @param beginIndex 分页时从第几条数据开始
	 * @param number 要从数据库中取几条数据
	 */
	public synchronized ArrayList<Object> queryByAttrs(Class clazz, String tableName,
			String[] columns, String[] attrNames, String[] selectionArgs,
			boolean isLike, String orderByAttrName, boolean isDesc,
			String beginIndex, String number) {
		Cursor cursor = this.commonQuery(tableName, columns, attrNames,
				selectionArgs, isLike, orderByAttrName, isDesc, beginIndex,
				number);
		ArrayList<Object> list = null;
		if (null != cursor) {
			try {
				list = getResultFromCursor(cursor, clazz);
			} catch (Exception e) {
				e.printStackTrace();
			}
			cursor.close();
		}
		return list;
	}
	
	/**
	 * 根据原始的sql语句来查询数据
	 * @param sql
	 * @param args
	 * @return
	 */
	public synchronized ArrayList<Object> queryBySql(Class clazz, String sql, String[] args) {
		DataBaseHelper helper = DataBaseHelper.getInstance(context);
		if(helper==null)
			return null;
		Cursor cursor = helper.getReadableDatabase().rawQuery(sql, args);
		ArrayList<Object> list = null;
		if (null != cursor) {
			try {
				list = getResultFromCursor(cursor, clazz);
			} catch (Exception e) {
				e.printStackTrace();
			}
			cursor.close();
		}
		helper.close();
		return list;
	}

	/**
	 * 根据Class对象从Cursor取出列表数据
	 */
	public ArrayList<Object> getResultFromCursor(Cursor cursor, Class clazz)
			throws IllegalAccessException, InstantiationException {
		ArrayList<Object> list = null;// 保存结果
		Object bean = null;// 保存一条查询结果
		int size = cursor.getColumnCount();// 查询返回的字段列数
		if (cursor.moveToFirst()) {
			list = new ArrayList<Object>();
			do {
				bean = clazz.newInstance();
				for (int index = 0; index < size; index++) {
					try {
						String columnName = cursor.getColumnName(index);// 获取指定列的字段名称
						if(columnName.equals("_id")||columnName.equals("CREATOR")){
							continue;
						}
						String value = cursor.getString(index);// 获取指定列的值
						Field field = clazz.getDeclaredField(columnName);
						setValueByReflect(bean, field, value);
					} catch (Exception e) {
						e.printStackTrace();
					}
				}
				list.add(bean);
			} while (cursor.moveToNext());
		}
		cursor.close();
		return list;
	}

	/**
	 * 通过反射为某个bean对象的字段填充内容
	 * @param bean  要填充的bean对象
	 * @param field bean对象的属性对象，这个field必须来自于上个bean参数
	 * @param value field对象要填充的值
	 */
	private void setValueByReflect(Object bean, Field field, String value)
			throws IllegalArgumentException, IllegalAccessException {
		field.setAccessible(true);
		if (String.class.equals(field.getType())) { // 字符串形式
			field.set(bean, value);
		} else if (long.class.equals(field.getType())) { //长整数
			field.set(bean, new Long(value));
		} else if (Integer.class.equals(field.getType())) { //包装整数
			field.set(bean, new Integer(value));
		} else if (int.class.equals(field.getType())) { // 整数
			field.set(bean, Integer.parseInt(value));
		}
	}

	/**
	 * 根据属性删除某条记录
	 * 
	 * @param tableName 数据库表名
	 * @param attrName  字段名称
	 * @param attrValue 字段值
	 * @return int 被删除的列数
	 */
	public synchronized int deleteByAttr(String tableName, String attrName, String attrValue) {
		DataBaseHelper helper = DataBaseHelper.getInstance(context);
		if(helper==null)
			return 0;
		SQLiteDatabase db = helper.getReadableDatabase();
		
		int number = db.delete(tableName, attrName + "=?",
				new String[] { attrValue });
		helper.close();
		return number;
	}
	
	/**
	 * 删除表中所有数据
	 * @param tableName
	 * @return
	 */
	public synchronized int deleteAll(String tableName){
		DataBaseHelper helper = DataBaseHelper.getInstance(context);
		if(helper==null)
			return 0;
		SQLiteDatabase db = helper.getReadableDatabase();
		int number = db.delete(tableName,null,null);
		helper.close();
		return number;
	}

	/**
	 * 多条件进行删除，包含某些不等于条件
	 * 
	 * @param tableName 表名
	 * @param attrNames  条件属性名称
	 * @param attrValues  条件属性值
	 * @param equals  是等于条件还是不等于,但各个属性之间为“与”链接 0:等于 1:不等于 2:大于
	 * @return
	 */
	public synchronized int deleteByAttrs(String tableName, String[] attrNames,
			String[] attrValues, String[] equals) {
		DataBaseHelper helper = DataBaseHelper.getInstance(context);
		if(helper==null)
			return 0;
		SQLiteDatabase db = helper.getReadableDatabase();
		
		String whereClause = "";
		for (int i = 0; i < attrNames.length; i++) {
			String equal = " = ";
			if ("1".equals(equals[i])) {
				equal = " <> ";
			} else if ("2".equals(equals[i])) {
				equal = " > ";
			}
			whereClause += attrNames[i] + equal + "?";
			if (i != attrNames.length - 1) {
				whereClause += " and ";
			}
		}
		int number = db.delete(tableName, whereClause, attrValues);
		helper.close();
		return number;
	}
}
